﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class NewsDAL : INewsDAL
    {
        #region SQL
        public const string sqlGetNews = "SELECT TbNews.*, TbNewsType.TypeName FROM TbNews LEFT OUTER JOIN TbNewsType ON TbNews.TypeID = TbNewsType.ID WHERE (1 = 1)";
        public const string sqlGetNewsByID = "SELECT * FROM TbNews WHERE (ID = {0})";
        public const string sqlGetNewsTop = "SELECT TOP ({0}) TbNews.*, TbNewsType.TypeName FROM TbNews LEFT OUTER JOIN TbNewsType ON TbNews.TypeID = TbNewsType.ID WHERE (1 = 1) ORDER BY TbNews.Seq DESC, TbNews.ID DESC";
        public const string sqlGetNewsTypes = "SELECT * FROM TbNewsType";
        public const string sqlGetNewsInGallery = "SELECT * FROM TbNews WHERE (IsInGallery = 1)";
        public const string sqlGetNewsIsHeadline = "SELECT TOP ({0}) * FROM (SELECT TOP (4) * FROM TbNews WHERE (IsHeadline = 1)) AS derivedtbl_1 " +
                                                   "UNION ALL " +
                                                   "SELECT TOP (6) * FROM TbNews AS TbNews_1 WHERE (IsHeadline = 2)";
        const string sqlUpdateNews = "UPDATE TbNews SET Title = {0}, ContentText = {1}, Pic = {2}, UpdateDate = {3}, TypeID = {4}, Seq={5}, IsInGallery = {6}, IsHeadline={7} WHERE (ID = {8})";
        const string sqlInsertNews = "INSERT INTO TbNews (Title, ContentText, Pic, UpdateDate, TypeID, Seq,IsInGallery,IsHeadline) VALUES ({0},{1},{2},{3},{4},{5},{6},{7})";
        const string sqlDeleteNews = "DELETE from TbNews where ID={0}";
        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetNews()
        {
            return DAO.Select(sqlGetNews);
        }

        public System.Data.DataTable GetNewsByID(int id)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ID", id)
            };
            string sql = string.Format(sqlGetNewsByID, "@ID");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetNewsTop(int num)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@num", num)
            };
            string sql = string.Format(sqlGetNewsTop, "@num");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetNewsInGallery()
        {
            return DAO.Select(sqlGetNewsInGallery);
        }

        public System.Data.DataTable GetNewsIsHeadline(int num)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@num", num)
            };
            string sql = string.Format(sqlGetNewsIsHeadline, "@num");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetNewsTypes()
        {
            return DAO.Select(sqlGetNewsTypes);
        }

        public bool UpdateNews(Model.News news)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@Title", news.Title),
                new SqlParameter ("@ContentText", news.ContentText),
                new SqlParameter ("@Pic", news.Pic),
                new SqlParameter ("@UpdateDate", news.UpdateDate),
                new SqlParameter ("@TypeID", news.TypeID),
                new SqlParameter ("@Seq", news.Seq),
                new SqlParameter ("@IsInGallery", news.IsInGallery),
                new SqlParameter ("@IsHeadline", news.IsHeadline),
                new SqlParameter ("@ID", news.ID)
            };
            string sql = string.Format(sqlUpdateNews, "@Title", "@ContentText", "@Pic", "@UpdateDate", "@TypeID", "@Seq", "@IsInGallery", "@IsHeadline", "@ID");
            return DAO.ExecuteSql(sql, sp);
        }

        public bool InsertNews(Model.News news)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@Title", news.Title),
                new SqlParameter ("@ContentText", news.ContentText),
                new SqlParameter ("@Pic", news.Pic),
                new SqlParameter ("@UpdateDate", news.UpdateDate),
                new SqlParameter ("@TypeID", news.TypeID),
                new SqlParameter ("@Seq", news.Seq),
                new SqlParameter ("@IsInGallery", news.IsInGallery),
                new SqlParameter ("@IsHeadline", news.IsHeadline)
            };
            string sql = string.Format(sqlInsertNews, "@Title", "@ContentText", "@Pic", "@UpdateDate", "@TypeID", "@Seq", "@IsInGallery", "@IsHeadline");
            return DAO.ExecuteSql(sql, sp);
        }

        public bool DeleteNews(int ID)
        {
            SqlParameter[] sp = new SqlParameter[] { 
                new SqlParameter ("@ID",ID),
            };
            string sql = string.Format(sqlDeleteNews, "@ID");
            return DAO.ExecuteSql(sql, sp);
        }

        public bool DeleteNews(List<int> IDs)
        {
            Dictionary<SqlParameter[], string> deleteSqls = new Dictionary<SqlParameter[], string>();
            for (int i = 0; i < IDs.Count; i++)
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter ("@ID",IDs[i])
                  };
                string sql = string.Format(sqlDeleteNews, "@ID");
                deleteSqls.Add(parms, sql);
            }
            return DAO.ExecuteSqlTran(deleteSqls);
        }
    }
}
